from flask_cors import CORS
import mysql.connector
from flask import Flask, jsonify, request

config = {
    "host": "127.0.0.1",
    "port": 3306,
    "database": "sql_store",
    "user": "root",
    "password": "system"
}

app = Flask(__name__)
cors = CORS(app)


@app.route("/api/products", methods=["delete"])
def deleteProducts():
    product_id = request.form["product_id"]

    conn = mysql.connector.connect(**config)
    curs = conn.cursor()

    curs.execute(f"""
    delete from products where product_id = {product_id}
    """)

    rowCount = curs.rowcount
    if rowCount > 0:
        conn.commit()

    curs.close()
    conn.close()

    jsonrst = []
    jsonrst.append({
        "rowcount": int(rowCount)
    })

    return jsonify(jsonrst)


@app.route("/api/products", methods=["put"])
def putProducts():
    product_id = request.form["product_id"]
    name = request.form["name"]
    quantity_in_stock = request.form["quantity_in_stock"]
    unit_price = request.form["unit_price"]

    conn = mysql.connector.connect(**config)
    curs = conn.cursor()

    curs.execute(f"""
    update products set
        name = '{name}',
        quantity_in_stock = {quantity_in_stock},
        unit_price = {unit_price}
    where
        product_id = {product_id}
    """)

    rowCount = curs.rowcount
    if rowCount > 0:
        conn.commit()

    curs.close()
    conn.close()

    jsonrst = []
    jsonrst.append({
        "rowcount": int(rowCount)
    })

    return jsonify(jsonrst)

# 单价不能大于100


@app.route("/api/products", methods=["post"])
def postProducts():
    name = request.form["name"]
    quantity_in_stock = request.form["quantity_in_stock"]
    unit_price = request.form["unit_price"]

    conn = mysql.connector.connect(**config)
    curs = conn.cursor()

    curs.execute(f"""
    insert into products (
        name,
        quantity_in_stock,
        unit_price
    )
    values (
        '{name}',
        {quantity_in_stock},
        {unit_price}
    )
    """)

    lastRowId = curs.lastrowid
    if lastRowId > 0:
        conn.commit()

    curs.close()
    conn.close()

    jsonrst = []
    jsonrst.append({
        "lastrowid": int(lastRowId)
    })

    return jsonify(jsonrst)

# /api/products/bac


@app.route("/api/products/<string:name>", methods=["get"])
def getProductsByName(name):
    conn = mysql.connector.connect(**config)
    curs = conn.cursor()

    curs.execute(f"""
    select
        product_id,
        name,
        quantity_in_stock,
        unit_price
    from
        products
    where
        name regexp '{name}'
    """)

    rst = curs.fetchall()
    jsonrst = []

    for r in rst:
        jsonrst.append({
            "product_id": int(r[0]),
            "name": r[1],
            "quantity_in_stock": int(r[2]),
            "unit_price": float(r[3])
        })

    curs.close()
    conn.close()

    return jsonify(jsonrst)

# /api/products/1


@app.route("/api/products/<int:id>", methods=["get"])
def getProductsById(id):
    conn = mysql.connector.connect(**config)
    curs = conn.cursor()

    curs.execute(f"""
    select
        product_id,
        name,
        quantity_in_stock,
        unit_price
    from
        products
    where
        product_id = {id}
    """)

    rst = curs.fetchall()
    jsonrst = []

    for r in rst:
        jsonrst.append({
            "product_id": int(r[0]),
            "name": r[1],
            "quantity_in_stock": int(r[2]),
            "unit_price": float(r[3])
        })

    curs.close()
    conn.close()

    return jsonify(jsonrst)


@app.route("/api/products", methods=["get"])
def getProducts():
    conn = mysql.connector.connect(**config)
    curs = conn.cursor()

    name = request.args.get("name")
    if name == None:
        curs.execute("""
        select
            product_id,
            name,
            quantity_in_stock,
            unit_price
        from
            products
        """)
    else:
        curs.execute(f"""
        select
            product_id,
            name,
            quantity_in_stock,
            unit_price
        from
            products
        where
            name regexp '{name}'
        """)

    rst = curs.fetchall()
    jsonrst = []

    for r in rst:
        jsonrst.append({
            "product_id": int(r[0]),
            "name": r[1],
            "quantity_in_stock": int(r[2]),
            "unit_price": float(r[3])
        })

    curs.close()
    conn.close()

    return jsonify(jsonrst)


if __name__ == "__main__":
    app.run(port=5000, debug=True)
